package com.library.dao;

import com.library.model.Borrow;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class BorrowDAO extends BaseDAO {

    // 1. 多条件查询借阅记录
    public List<Borrow> findByCondition(String username, String bookTitle, String status) {
        List<Borrow> borrows = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        // 动态构建SQL（支持模糊查询）
        StringBuilder sql = new StringBuilder("SELECT * FROM borrow WHERE 1=1");
        List<Object> params = new ArrayList<>();

        if (username != null && !username.isEmpty()) {
            sql.append(" AND username LIKE ?");
            params.add("%" + username + "%"); // 模糊匹配
        }
        if (bookTitle != null && !bookTitle.isEmpty()) {
            sql.append(" AND book_title LIKE ?");
            params.add("%" + bookTitle + "%");
        }
        if (status != null && !status.isEmpty()) {
            sql.append(" AND status = ?");
            params.add(status);
        }

        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql.toString());
            // 设置参数
            for (int i = 0; i < params.size(); i++) {
                ps.setObject(i + 1, params.get(i));
            }
            rs = ps.executeQuery();

            // 封装结果集到实体类
            while (rs.next()) {
                Borrow borrow = new Borrow();
                borrow.setId(rs.getInt("id"));
                borrow.setUsername(rs.getString("username"));
                borrow.setBookTitle(rs.getString("book_title"));
                borrow.setIsbn(rs.getString("isbn"));
                borrow.setFinePerDay(rs.getDouble("fine_per_day"));
                borrow.setBorrowDate(rs.getDate("borrow_date"));
                borrow.setExpectReturnDate(rs.getDate("expect_return_date"));
                borrow.setStatus(rs.getString("status"));

                // 计算是否逾期（未归还且当前时间>预计归还时间）
                if ("UNRETURNED".equals(borrow.getStatus())) {
                    Date now = new Date();
                    borrow.setOverdue(now.after(borrow.getExpectReturnDate()));
                } else {
                    borrow.setOverdue(false); // 已归还默认不逾期
                }

                borrows.add(borrow);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn, ps, rs);
        }
        return borrows;
    }

    // 2. 新增借阅记录
    public void add(Borrow borrow) {
        String sql = "INSERT INTO borrow " +
                "(username, book_title, isbn, fine_per_day, borrow_date, expect_return_date, status) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?)";
        try {
            executeUpdate(sql,
                    borrow.getUsername(),
                    borrow.getBookTitle(),
                    borrow.getIsbn(),
                    borrow.getFinePerDay(),
                    borrow.getBorrowDate(),
                    borrow.getExpectReturnDate(),
                    borrow.getStatus()
            );
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 3. 更新借阅记录
    public void update(Borrow borrow) {
        String sql = "UPDATE borrow SET " +
                "username=?, book_title=?, isbn=?, fine_per_day=?, " +
                "borrow_date=?, expect_return_date=?, status=? " +
                "WHERE id=?";
        try {
            executeUpdate(sql,
                    borrow.getUsername(),
                    borrow.getBookTitle(),
                    borrow.getIsbn(),
                    borrow.getFinePerDay(),
                    borrow.getBorrowDate(),
                    borrow.getExpectReturnDate(),
                    borrow.getStatus(),
                    borrow.getId()
            );
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 4. 删除借阅记录
    public void delete(int id) {
        String sql = "DELETE FROM borrow WHERE id=?";
        try {
            executeUpdate(sql, id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 5. 办理归还（更新状态+计算逾期罚款，同时同步到归还记录表）
    public void updateReturnStatus(int borrowId, Date returnDate) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = getConnection();
            conn.setAutoCommit(false); // 开启事务

            // 步骤1：查询借阅记录详情（用于计算逾期）
            String querySql = "SELECT * FROM borrow WHERE id=?";
            ps = conn.prepareStatement(querySql);
            ps.setInt(1, borrowId);
            rs = ps.executeQuery();

            if (rs.next()) {
                // 获取借阅信息
                String username = rs.getString("username");
                String bookTitle = rs.getString("book_title");
                Date expectReturnDate = rs.getDate("expect_return_date");
                double finePerDay = rs.getDouble("fine_per_day");
                Date borrowDate = rs.getDate("borrow_date");

                // 步骤2：计算逾期天数和罚款金额
                long overdueDays = 0;
                if (returnDate.after(expectReturnDate)) { // 实际归还日期 > 预计归还日期
                    long diffMs = returnDate.getTime() - expectReturnDate.getTime();
                    overdueDays = diffMs / (1000 * 60 * 60 * 24); // 转换为天数
                }
                double fineAmount = overdueDays * finePerDay;

                // 步骤3：更新借阅记录状态为"已归还"
                String updateBorrowSql = "UPDATE borrow SET status='RETURNED' WHERE id=?";
                ps = conn.prepareStatement(updateBorrowSql);
                ps.setInt(1, borrowId);
                ps.executeUpdate();

                // 步骤4：同步新增一条归还记录到return_record表
                String insertReturnSql = "INSERT INTO return_record " +
                        "(username, book_title, borrow_date, expect_return_date, return_date, overdue_days, fine_amount) " +
                        "VALUES (?, ?, ?, ?, ?, ?, ?)";
                ps = conn.prepareStatement(insertReturnSql);
                ps.setString(1, username);
                ps.setString(2, bookTitle);
                ps.setDate(3, new java.sql.Date(borrowDate.getTime()));
                ps.setDate(4, new java.sql.Date(expectReturnDate.getTime()));
                ps.setDate(5, new java.sql.Date(returnDate.getTime()));
                ps.setLong(6, overdueDays);
                ps.setDouble(7, fineAmount);
                ps.executeUpdate();

                conn.commit(); // 提交事务
            }
        } catch (SQLException e) {
            try {
                if (conn != null) conn.rollback(); // 事务回滚
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) conn.setAutoCommit(true); // 恢复自动提交
            } catch (SQLException e) {
                e.printStackTrace();
            }
            close(conn, ps, rs);
        }
    }
}
